//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR10
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.*;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import static java.sql.Types.*;

/**
 * <code>Broker</code> for <code>RegisteredPatronAccountBean</code>.
 */
public class RegisteredPatronAccountBroker extends Broker<RegisteredPatronAccountBean> {
	
	/* Singleton Constructor */
	
	private static RegisteredPatronAccountBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private RegisteredPatronAccountBroker() throws ErrorBean {
		insertSQL =
		updateSQL = 
		validateSQL = null;
		checkUsernameSQL = null;
		deleteRegisteredPatronSQL = null;
	}
	
	/**
	 * Retrieves the static RegisteredPatronAccountBroker instance.
	 * @return static RegisteredPatronAccountBroker
	 * @throws ErrorBean
	 */
	public synchronized static RegisteredPatronAccountBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new RegisteredPatronAccountBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private CallableStatement insertSQL;
	private CallableStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "CALL sp_create_registered_patron(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	}
	
	private CallableStatement validateSQL;	
	private CallableStatement getValidateSQL () throws ErrorBean {
		CallableStatement statement = prepare(validateSQL, "CALL sp_validate_patron(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		registerOutParameter(statement, 3, BIGINT);
		registerOutParameter(statement, 4, VARCHAR);
		registerOutParameter(statement, 5, VARCHAR);
		registerOutParameter(statement, 6, VARCHAR);
		registerOutParameter(statement, 7, VARCHAR);
		registerOutParameter(statement, 8, VARCHAR);
		registerOutParameter(statement, 9, VARCHAR);
		registerOutParameter(statement, 10, VARCHAR);
		registerOutParameter(statement, 11, VARCHAR);
		registerOutParameter(statement, 12, VARCHAR);
		return statement;
	}
	
	private CallableStatement updateSQL;
	private CallableStatement getUpdateSQL () throws ErrorBean {
		return prepare (updateSQL, "CALL sp_update_registered_patron(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	}
	
	private PreparedStatement deleteRegisteredPatronSQL;
	private PreparedStatement getDeleteRegisteredPatronSQL () throws ErrorBean {
		return prepare(deleteRegisteredPatronSQL, "UPDATE registered_patron SET active = FALSE WHERE fk_patron_id = ?");
	}
	
	private CallableStatement checkUsernameSQL;
	private CallableStatement getCheckUsernameSQL () throws ErrorBean {
		CallableStatement statement = prepare(checkUsernameSQL, "CALL sp_check_username(?,?)");
		registerOutParameter(statement, 2, BOOLEAN);
		return statement;
	}
	
	
	
	/* END Prepared Statements */
	
	
	/* Methods */
	
	/**
	 * Validates the login information of the <code>RegisteredPatronAccountBean</code>.
	 * @param loginBean the <code>RegisteredPatronAccountBean</code> containing the information to
	 * @return The full bean representing the <code>Patron</code>
	 * @throws ErrorBean if an incorrect username or password was supplied 
	 */
	public RegisteredPatronAccountBean validateLogon (RegisteredPatronAccountBean loginBean) throws ErrorBean {
		String username = loginBean.getUsername();
		String password = loginBean.getPassword();
		
		try {
			CallableStatement validateSQL = getValidateSQL(); 
			validateSQL.setString(1, username);
			validateSQL.setString(2, password);
			validateSQL.executeQuery();
			int id = validateSQL.getInt(3);
			if (id == 0)
				throwError(
					PATRON_INCORRECT_USERNAME_OR_PASSWORD,
					this);
			
			RegisteredPatronAccountBean pab =
				new RegisteredPatronAccountBean(
					id,
					username,
					validateSQL.getString(4),
					validateSQL.getString(5),
					validateSQL.getString(6),
					validateSQL.getString(7),
					validateSQL.getString(8),
					validateSQL.getString(9),
					validateSQL.getString(10),
					validateSQL.getString(11),
					validateSQL.getDate(12));
			return pab;
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
			return null;
		}
	}
	
	/* END Methods */
	
	
	/* (Broker) Methods */
	
	@Override
	public boolean persist (RegisteredPatronAccountBean rpab) throws ErrorBean {		
		rpab.isValid();
		
		int patron_id = rpab.getId();
		String first_name = rpab.getFirstname();
		String last_name = rpab.getLastname();
		String username = rpab.getUsername();
		String password = rpab.getPassword();
		String email = rpab.getEmail();
		String street = rpab.getStreet();
		String city = rpab.getCity();
		String province = rpab.getProvince();
		String postal_code = rpab.getPostalCode();
		String phone = rpab.getPhone();
				
		if (patron_id == 0) {
			if (usernameExists(username))
				throwError(
					PATRON_USERNAME_DUPLICATE,
					username,
					this);
			try {
				CallableStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, first_name);
				insertSQL.setString(2, last_name);
				insertSQL.setString(3, phone);
				insertSQL.setString(4, username);
				insertSQL.setString(5, password);
				insertSQL.setString(6, email);
				insertSQL.setString(7, street);
				insertSQL.setString(8, city);
				insertSQL.setString(9, province);
				insertSQL.setString(10, postal_code);
				insertSQL.setBoolean(11, true);
				insertSQL.execute();			
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		}
		else if (patron_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, first_name);
				updateSQL.setString(2, last_name);
				updateSQL.setString(3, phone);
				updateSQL.setString(4, username);
				updateSQL.setString(5, password);
				updateSQL.setString(6, email);
				updateSQL.setString(7, street);
				updateSQL.setString(8, city);
				updateSQL.setString(9, province);
				updateSQL.setString(10, postal_code);
				updateSQL.setBoolean(11, true);
				updateSQL.setInt(12, patron_id);
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}		
		return false;
	}

	@Override
	public boolean remove(RegisteredPatronAccountBean rpab) throws ErrorBean {
		int patron_id = rpab.getId();
		
		try {
			PreparedStatement deleteRegisteredPatronSQL = getDeleteRegisteredPatronSQL();
			deleteRegisteredPatronSQL.setInt(1, patron_id);
			deleteRegisteredPatronSQL.execute();			
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public List<RegisteredPatronAccountBean> search(RegisteredPatronAccountBean searchBean) throws ErrorBean {
		List<RegisteredPatronAccountBean> search = new ArrayList<RegisteredPatronAccountBean>();
		RegisteredPatronAccountBean rpab;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM patron JOIN registered_patron WHERE patron_id = fk_patron_id");
		
		//Search by Patron first name
		String first_name = searchBean.getFirstname();
		if (!"".equals(first_name)) {
			sql.append(" AND UPPER(first_name) LIKE UPPER('%" + first_name + "%')");
		}
		
		//Search by Patron last name
		String last_name = searchBean.getLastname();
		if (!"".equals(last_name)) {
			sql.append(" AND UPPER(last_name) LIKE UPPER('%" + last_name + "%')");
		}
		
		//Search by Patron username
		String username = searchBean.getUsername();
		if (!"".equals(username)) {
			sql.append(" AND username = '" + username + "'");
		}
		
		//Search by Patron phone number
		String phone = searchBean.getPhone();
		if (!"".equals(phone)) {
			sql.append(" AND phone LIKE '%" + phone + "%'");
		}
		
		//Search by Patron email
		String email = searchBean.getEmail();
		if (!"".equals(email)) {
			sql.append(" AND email = '" + email + "'");
		}
		
		//Search by Patron city
		String city = searchBean.getCity();
		if (!"".equals(city)) {
			sql.append(" AND UPPER(city) LIKE UPPER('%" + city + "%')");
		}
		
		//Search by Patron province
		String province = searchBean.getProvince();
		if (!"".equals(province)) {
			sql.append(" AND UPPER(province) = UPPER('" + province + "')");
		}
		
		//Search by Patron postal code
		String postal_code = searchBean.getPostalCode();
		if (!"".equals(postal_code)) {
			sql.append(" AND UPPER(postal_code) = UPPER('" + postal_code + "')");
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					rpab = new RegisteredPatronAccountBean();
					rpab.setId(results.getInt("patron_id"));
					rpab.setFirstname(results.getString("first_name"));
					rpab.setLastname(results.getString("last_name"));
					rpab.setUsername(results.getString("username"));
					rpab.setPassword(results.getString("password"));
					rpab.setEmail(results.getString("email"));
					rpab.setStreet(results.getString("street"));
					rpab.setCity(results.getString("city"));
					rpab.setPostalCode(results.getString("postal_code"));
					rpab.setProvince(results.getString("province"));
					rpab.setPhone(results.getString("phone"));
					rpab.setDateEntered(results.getDate("date_entered"));
					
					search.add(rpab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public List<RegisteredPatronAccountBean> listAll() throws ErrorBean {
		List<RegisteredPatronAccountBean> search = new ArrayList<RegisteredPatronAccountBean>();
		RegisteredPatronAccountBean rpab;

		StringBuilder sql = new StringBuilder("SELECT * FROM patron JOIN registered_patron WHERE patron_id = fk_patron_id;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					rpab = new RegisteredPatronAccountBean();
					rpab.setId(results.getInt("patron_id"));
					rpab.setFirstname(results.getString("first_name"));
					rpab.setLastname(results.getString("last_name"));
					rpab.setUsername(results.getString("username"));
			
					rpab.setEmail(results.getString("email"));
					rpab.setStreet(results.getString("street"));
					rpab.setCity(results.getString("city"));
					rpab.setPostalCode(results.getString("postal_code"));
					rpab.setProvince(results.getString("province"));
					rpab.setPhone(results.getString("phone"));

					
					search.add(rpab);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Local Methods */
	
	/**
	 * Checks if a username currently exists in the database.
	 * @param username the username being checked for existence
	 * @return true if the username already exists, false if it does not
	 * @throws ErrorBean
	 */
	private synchronized boolean usernameExists (String username) throws ErrorBean {
		boolean exists = false;
		try {			
			CallableStatement checkUsernameSQL = getCheckUsernameSQL();
			checkUsernameSQL.setString(1, username);
			checkUsernameSQL.executeQuery();
			exists = checkUsernameSQL.getBoolean(2);

		} catch(SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		
		return !exists;
	}
	
	/* END Local Methods */
}
